use diesel::{
    AsChangeset, Connection as _, ExpressionMethods as _, Insertable, QueryDsl as _, Queryable,
    RunQueryDsl as _, Selectable, SqliteConnection,
};

use crate::schema::users;

mod schema {
    diesel::table! {
        users {
            id -> Integer,
            name -> Text,
            hair_color -> Nullable<Text>,
        }
    }
}

#[derive(Queryable, Selectable, Default, AsChangeset, Debug, Insertable, Clone)]
#[diesel(table_name = users)]
#[diesel(check_for_backend(diesel::sqlite::Sqlite))]
pub struct UsersEntity {
    id: i32,
    name: String,
    hair_color: Option<String>,
}

fn main() {
    use schema::users::{dsl as UsersDSL, dsl::users as Users};

    let mut conn =
        SqliteConnection::establish("tmp/user.db").expect("error connecting to database");

    // 创建表
    let _ = diesel::sql_query(
        "CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  hair_color TEXT,
);",
    )
    .execute(&mut conn);

    // 增
    let _ = diesel::insert_into(Users)
        .values((UsersDSL::name.eq("Sean"), UsersDSL::hair_color.eq("白色")))
        .execute(&mut conn);

    let user = UsersEntity {
        name: "hahaha".to_string(),
        hair_color: Some("黄色".to_owned()),
        ..Default::default()
    };

    let _ = diesel::insert_into(Users).values(&user).execute(&mut conn);

    let _ = diesel::insert_into(Users)
        .values(vec![user.clone(), user.clone()])
        .execute(&mut conn);

    // 删
    let filter = Users.filter(UsersDSL::name.eq("花花"));
    let _ = diesel::delete(filter).execute(&mut conn);

    // 删除所有
    // let _ = diesel::delete(Users).execute(conn);

    // 改
    let update_user = UsersEntity {
        name: "完达山1号".to_string(),
        hair_color: Some("黄色".to_owned()),
        ..Default::default()
    };
    let filter = Users.filter(UsersDSL::name.eq("Sean"));

    let _ = diesel::update(filter).set(update_user).execute(&mut conn);

    // 查
    let mut st = Users.filter(UsersDSL::hair_color.eq("黄色")).into_boxed();
    if 1 == 1 {
        st = st.limit(2);
    }

    if 2 == 2 {
        st = st.order(UsersDSL::id.desc())
    }

    let users: Vec<UsersEntity> = st.load(&mut conn).unwrap();
    println!("users : {:?}", users);
}
